from datetime import datetime
import utils.utils as utils
import time

class genericDB(object):

    def setConnection(self, host, database, user='', password=''):
        pass

 
    
    def executeQuery(self, connection, query):
        resultRows =None
        rowAffected = 0
        header = None
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            self.logger.info('Sucsess: %s: row affected %s' % (query, rowAffected))
            rowAffected = cursor.rowcount
            description = cursor.description
            if description:
                header = [row[0].upper() for row in description]
                resultRows = [row for row in cursor]
        except Exception as e:
            connection.rollback()
            self.logger.error('Problem with execution  query: %s, %s' % (query, e))
        else:
            connection.commit()
            self.logger.info('Query commited')
        finally:
            cursor.close()
        return (resultRows, header, rowAffected)
    
    def executeSQLList(self, connection, sqlList, resultFile='', fileDate=False):
        if fileDate:
            dataString =  time.strftime("_%Y%m%H%M%S", time.gmtime())
            resultFile = resultFile + dataString + '.txt'
        else:
            resultFile = resultFile +  '.txt'
            
            
        for line in sqlList:
            segment = line.upper().split(':')
            result=header=rowsAffected=None
            if not 'NOSQL' in segment:
                (result, header, rowsAffected)=self.executeQuery(connection, segment[-1])
            if resultFile:
                resultfile = open(resultFile, 'a')
                if 'DATE' in segment:
                    resultfile.write('\nDate:\t %s ' % (str(datetime.now())))
                if not 'NOINFO' in segment:
                    resultfile.write('\nResult from processing query: %s' % segment[-1])
                    resultfile.write('\nRows affected %d\n\n' % rowsAffected)
                if 'PRINT' in segment:
                    resultfile.write('%s' % (segment[-1]))
                resultfile.close()
                if result:
                    resultfile = open(resultFile, 'a')
                    resultfile.write('\n\n') 
                    resultfile.write('%s' % '\t'.join(header))
                    resultfile.close()
                    utils.writeList2DToFile(resultFile, result)
    
    

    def executeSQLwithCSV(self, connection, csvFilePath, sqlFilePath, testbatch=False):
        csvDict = utils.csvIntoDict(csvFilePath)
        sqlList = utils.parseFileIntoSQLList(sqlFilePath)
        batchQueries = []
        for sqlLine in  sqlList:
            for csvParm in csvDict:
                batchQueries.append(sqlLine % csvParm)
        self.logger.debug('Sql commpiled list: %s' % batchQueries)
        resultRows =None
        rowAffected = 0
        header = None
        try:
            cursor = connection.cursor()
            for sqlQuery in batchQueries:
                cursor.execute(sqlQuery)
                rowAffected = cursor.rowcount
                self.logger.info('Sucsess: %s: row affected %s' % (sqlQuery, rowAffected))
#                description = cursor.description
#                resultRows = [row for row in cursor]
        except Exception as e:
            connection.rollback()
            self.logger.error('Problem with execution  query: %s, %s' % (sqlQuery, e))
        else:
            if testbatch:
                self.logger.info('Batch not commited')
            else:
                connection.commit()
                self.logger.info('Batch commited')
        finally:
            cursor.close()
        
        





